﻿package tech.deepmind.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import tech.deepmind.entity.User;

/**
 * dao层抛出异常，让service层去捕获处理
 * @author Administrator
 *
 */
@Repository
public class UserDaoImpl implements UserDao{
	
	@Autowired
	private BaseDao baseDao;
	
	@Override
	public int add( User user)  {
		int updateRows = 0;
		String sql = "insert into smbms_user (userCode,userName,userPassword," +
				"userRole,gender,birthday,phone,address,creationDate,createdBy) " +
				"values(?,?,?,?,?,?,?,?,?,?)";
		Object[] params = {user.getUserCode(),user.getUserName(),user.getUserPassword(),
						user.getUserRole(),user.getGender(),user.getBirthday(),
						user.getPhone(),user.getAddress(),user.getCreationDate(),user.getCreatedBy()};
		try {
			baseDao.dopstmt(sql, params);
			updateRows = baseDao.getUpdateCount();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			baseDao.closeAll(null);
		}
			
		return updateRows;
	}

	@Override
	public User getLoginUser(String userCode)
			 {
		// TODO Auto-generated method stub
		
		ResultSet rs = null;
		User user = null;
		
		String sql = "select * from smbms_user where userCode=?";
		Object[] params = {userCode};
		try {
			baseDao.dopstmt(sql, params);
			rs = baseDao.getResultSet();
			if(rs.next()){
				user = new User();
				user.setId(rs.getInt("id"));
				user.setUserCode(rs.getString("userCode"));
				user.setUserName(rs.getString("userName"));
				user.setUserPassword(rs.getString("userPassword"));
				user.setGender(rs.getInt("gender"));
				user.setBirthday(rs.getDate("birthday"));
				user.setPhone(rs.getString("phone"));
				user.setAddress(rs.getString("address"));
				user.setUserRole(rs.getInt("userRole"));
				user.setCreatedBy(rs.getInt("createdBy"));
				user.setCreationDate(rs.getTimestamp("creationDate"));
				user.setModifyBy(rs.getInt("modifyBy"));
				user.setModifyDate(rs.getTimestamp("modifyDate"));
				user.setIdPicPath(rs.getString("idPicPath"));
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			baseDao.closeAll(rs);
		}
		return user;
	}

	@Override
	public List<User> getUserList(String userName,int userRole,int currentPageNo, int pageSize) {
		ResultSet rs = null;
		List<User> userList = new ArrayList<User>();
		
		StringBuffer sql = new StringBuffer();
		sql.append("select u.*,r.roleName as userRoleName from smbms_user u,smbms_role r where u.userRole = r.id");
		List<Object> list = new ArrayList<Object>();
		if(!StringUtils.isEmpty(userName)){
			sql.append(" and u.userName like ?");
			list.add("%"+userName+"%");
		}
		if(userRole > 0){
			sql.append(" and u.userRole = ?");
			list.add(userRole);
		}
		sql.append(" order by id DESC limit ?,?");
		currentPageNo = (currentPageNo-1)*pageSize;
		list.add(currentPageNo);
		list.add(pageSize);
		
		Object[] params = list.toArray();
		try {
			baseDao.dopstmt(sql.toString(), params);
			rs = baseDao.getResultSet();
			while(rs.next()){
				User _user = new User();
				_user.setId(rs.getInt("id"));
				_user.setUserCode(rs.getString("userCode"));
				_user.setUserName(rs.getString("userName"));
				_user.setGender(rs.getInt("gender"));
				_user.setBirthday(rs.getDate("birthday"));
				_user.setPhone(rs.getString("phone"));
				_user.setUserRole(rs.getInt("userRole"));
				_user.setUserRoleName(rs.getString("userRoleName"));
				_user.setIdPicPath(rs.getString("idPicPath"));
				userList.add(_user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			baseDao.closeAll(rs);
		}
		return userList;
	}

	@Override
	public int deleteUserById(Integer delId)  {
		int flag = 0;
		String sql = "delete from smbms_user where id=?";
		Object[] params = {delId};
		try {
			baseDao.dopstmt(sql, params);
			flag = baseDao.getUpdateCount();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			baseDao.closeAll(null);
		}
		return flag;
	}

	@Override
	public User getUserById(String id)  {
		User user = null;
		
		ResultSet rs = null;
		
		String sql = "select u.*,r.roleName as userRoleName from smbms_user u,smbms_role r where u.id=? and u.userRole = r.id";
		Object[] params = {id};
		
		try {
			baseDao.dopstmt(sql, params);
			rs = baseDao.getResultSet();
			if(rs.next()){
				user = new User();
				user.setId(rs.getInt("id"));
				user.setUserCode(rs.getString("userCode"));
				user.setUserName(rs.getString("userName"));
				user.setUserPassword(rs.getString("userPassword"));
				user.setGender(rs.getInt("gender"));
				user.setBirthday(rs.getDate("birthday"));
				user.setPhone(rs.getString("phone"));
				user.setAddress(rs.getString("address"));
				user.setUserRole(rs.getInt("userRole"));
				user.setCreatedBy(rs.getInt("createdBy"));
				user.setCreationDate(rs.getTimestamp("creationDate"));
				user.setModifyBy(rs.getInt("modifyBy"));
				user.setModifyDate(rs.getTimestamp("modifyDate"));
				user.setUserRoleName(rs.getString("userRoleName"));
				user.setIdPicPath(rs.getString("idPicPath"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			baseDao.closeAll(rs);
		}
		return user;
	}

	@Override
	public int modify(User user)  {
		int flag = 0;
		String sql = "update smbms_user set userName=?,gender=?,birthday=?,phone=?,address=?,userRole=?,modifyBy=?,modifyDate=? where id = ? ";
		Object[] params = {user.getUserName(),user.getGender(),user.getBirthday(),
				user.getPhone(),user.getAddress(),user.getUserRole(),user.getModifyBy(),
				user.getModifyDate(),user.getId()};
		try {
			baseDao.dopstmt(sql, params);
			flag = baseDao.getUpdateCount();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			baseDao.closeAll(null);
		}
		return flag;
	}

	@Override
	public int updatePwd(int id, String pwd) {
		int flag = 0;
		String sql = "update smbms_user set userPassword= ? where id = ?";
		Object[] params = {pwd,id};
		try {
			baseDao.dopstmt(sql, params);
			flag = baseDao.getUpdateCount();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			baseDao.closeAll(null);
		}
		return flag;
	}

	@Override
	public int getUserCount(String userName, int userRole) {
		ResultSet rs = null;
		int count = 0;
		
		StringBuffer sql = new StringBuffer();
		sql.append("select count(1) as count from smbms_user u,smbms_role r where u.userRole = r.id");
		List<Object> list = new ArrayList<Object>();
		if(!StringUtils.isEmpty(userName)){
			sql.append(" and u.userName like ?");
			list.add("%"+userName+"%");
		}
		if(userRole > 0){
			sql.append(" and u.userRole = ?");
			list.add(userRole);
		}
		Object[] params = list.toArray();
		try {
			baseDao.dopstmt(sql.toString(), params);
			rs=baseDao.getResultSet();
			if(rs.next()){
				count = rs.getInt("count");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			baseDao.closeAll(null);
		}
		return count;
	}

	
}
